import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
from reportlab.lib.utils import ImageReader
import os

# 5. 字体配置 (解决中文显示问题)
plt.rcParams['font.sans-serif'] = ['Noto Serif SC','Microsoft YaHei', 'WenQuanYi Micro Hei', 'sans-serif']
plt.rcParams['axes.unicode_minus'] = False

# -----------------------------
# 🔹 模块1：数据读取与预处理
# -----------------------------
def load_and_preprocess_data(file_path):
    """
    读取Excel数据并预处理
    """
    df = pd.read_excel(file_path)
    df['检测日期'] = pd.to_datetime(df['检测日期'])  # 格式化日期
    return df

# -----------------------------
# 🔹 模块2：统计值计算
# -----------------------------
def calculate_stats(df, room, particle_col):
    """
    计算指定房间的统计值
    """
    values = df[df['房间名称'] == room][particle_col]
    return {
        'Max': values.max(),
        'Min': values.min(),
        'Ave': values.mean(),
        'SD': values.std(),
        'Ave-3SD': values.mean() - 3 * values.std(),
        'Ave+3SD': values.mean() + 3 * values.std()
    }

# -----------------------------
# 🔹 模块3：趋势图绘制
# -----------------------------
def plot_trend_chart(df, room, particle_col, output_dir):
    """
    绘制趋势图并保存
    """
    room_data = df[df['房间名称'] == room]
    dates = room_data['检测日期'].dt.strftime('%Y-%m')
    values = room_data[particle_col]

    plt.figure(figsize=(10, 6))
    plt.plot(dates, values, marker='o', label='实测值', color='blue', linewidth=2)

    # 添加限值线
    ucl = room_data['UCL'].iloc[0]
    alert = room_data['警戒限'].iloc[0]
    action = room_data['行动限'].iloc[0]
    stats = calculate_stats(df, room, particle_col)

    plt.axhline(stats['Ave-3SD'], color='gray', linestyle='--', label='Ave-3SD')
    plt.axhline(stats['Ave+3SD'], color='gray', linestyle='--', label='Ave+3SD')
    plt.axhline(ucl, color='red', linestyle='-', linewidth=2, label='UCL')
    plt.axhline(alert, color='gold', linestyle='-.', label='警戒线')
    plt.axhline(action, color='orange', linestyle='-.', label='行动限')

    # 图表样式
    plt.title(f'{room} - {particle_col} 粒子趋势图', fontsize=14, fontweight='bold')
    plt.xlabel('检测日期', fontsize=12)
    plt.ylabel('粒子浓度 (#/m³)', fontsize=12)
    plt.legend()
    plt.grid(True, linestyle='--', alpha=0.6)
    plt.tight_layout()

    # 保存图像
    os.makedirs(output_dir, exist_ok=True)
    filename = f"{room}_{particle_col.replace('μ', 'um')}.png"
    plt.savefig(os.path.join(output_dir, filename), dpi=300)
    plt.close()
    return filename

# -----------------------------
# 🔹 模块4：生成PDF报告
# -----------------------------
def generate_pdf_report(df, image_dir, output_path):
    """
    生成包含统计表和图表的PDF报告
    """
    c = canvas.Canvas(output_path, pagesize=letter)
    width, height = letter
    c.setFont("Helvetica-Bold", 16)
    c.drawString(50, height - 50, "医药企业年度尘埃粒子监测报告")
    c.setFont("Helvetica", 12)

    y = height - 80
    for idx, row in df.iterrows():
        if y < 100:
            c.showPage()
            y = height - 50

        # =============这块代码存在逻辑问题==============
        text = f"{row['房间名称']} - {row['粒径']}: Ave={row['Ave']:.1f}, UCL={row['UCL']}"
        c.drawString(50, y, text)
        y -= 20

        # 插入对应图片
        img_file = f"{row['房间名称']}_{row['粒径'].replace('μ', 'um')}.png"
        img_path = os.path.join(image_dir, img_file)
        if os.path.exists(img_path):
            c.drawImage(ImageReader(img_path), 50, y - 300, width=500, height=300)
            y -= 320

    c.save()
    print(f"✅ PDF报告已生成：{output_path}")

# -----------------------------
# 🔹 主程序入口
# -----------------------------
def main(file_path, output_dir="reports"):
    # 1. 读取数据
    df = load_and_preprocess_data(file_path)

    # 2. 配置粒径列名
    particle_cols = ['0.5μm']  # 可扩展为 ['0.5μm', '5.0μm'] 等

    # 3. 生成统计表和图表
    results = []
    for room in df['房间名称'].unique():
        for col in particle_cols:
            stats = calculate_stats(df, room, col)
            ucl = df[df['房间名称'] == room]['UCL'].iloc[0]
            alert = df[df['房间名称'] == room]['警戒限'].iloc[0]
            action = df[df['房间名称'] == room]['行动限'].iloc[0]
            results.append({
                '房间': room,
                '粒径': col,
                'Max': stats['Max'],
                'Min': stats['Min'],
                'Ave': stats['Ave'],
                'SD': stats['SD'],
                'Ave-3SD': stats['Ave-3SD'],
                'Ave+3SD': stats['Ave+3SD'],
                'UCL': ucl,
                '警戒线': alert,
                '行动限': action
            })
            plot_trend_chart(df, room, col, os.path.join(output_dir, "figures"))

    result_df = pd.DataFrame(results)
    result_df.to_excel(os.path.join(output_dir, "统计汇总.xlsx"), index=False)
    print("✅ 统计表已导出至：reports/统计汇总.xlsx")

    # 4. 生成PDF报告
    # generate_pdf_report(result_df, os.path.join(output_dir, "figures"), os.path.join(output_dir, "年度监测报告.pdf"))

# -----------------------------
# 🔹 运行主程序
# -----------------------------
if __name__ == "__main__":
    # 替换为你的实际Excel文件路径
    main(r"C:\Users\zgy\Desktop\code\data.xlsx")